package DB;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import Gui.Terminal;
import Model.GunReplicas;
import Model.Product;
/**
 * 
 * @author Allan Malka
 *
 */
public class DBGunReplicas 
{
	//If anything changes in the database, then this should be easy to find and edit:
			private final String productID = "productID";
			private final String name = "name";
			private final String productType = "ProductType";
			private final String purchasePrice = "purchasePrice";
			private final String salesPrice = "salesPrice";
			private final String countryOfOrigin = "countryOfOrigin";
			private final String stock = "stock";
			private final String minStock = "minStock";
			//GunReplicas
			private final String fabric = "fabric";
			private final String calibre = "calibre";
			//DBProduct connection
			private DBProduct dbP;
			
			//String builder
			private final String selectAll = String.format("SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s FROM Product INNER JOIN GunReplicas ON Product.ProductID = GunReplicas.GprodID", 
															productID, name, productType, purchasePrice, salesPrice, countryOfOrigin, stock, minStock, fabric, calibre);
			/*private final String createProduct = String.format("INSERT INTO Product %s, %s, %s, %s, %s, %s, %s VALUES ", 
																productID, name, purchasePrice, salesPrice, countryOfOrigin, stock, minStock);*/
			private final String createGunReplicas = String.format("INSERT INTO GunReplicas %s, %s, %s VALUES ", productID, fabric, calibre);
			private final String deleteGunReplicas = "DELETE FROM Product INNER JOIN GunReplicas ON Product.ProductID = GunReplicas.GProdID";
			
			private DBConnection conn;
			
			public DBGunReplicas()
			{
				conn = DBConnection.getInstance();
				dbP = new DBProduct();
			}
			
			public GunReplicas getGunsReplicas(String _prodID)
			{
				GunReplicas result = null;
				
				//Create statement
				Statement stmt = null;
				try {
					stmt = conn.getDBcon().createStatement();
				}
				catch(SQLException sE) {
					Terminal.say("An error occured during statement creation. \n Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Execute statement
				String condition = WhereAdder(selectAll, productID + " = " +_prodID);
				try {
					stmt.execute(condition);
				}
				catch(SQLException sE) {
					Terminal.say("An error occured executing the statement.");
					Terminal.say(condition);
				    Terminal.say("Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Get result
				ResultSet resSet = null;
				try {
					resSet = stmt.getResultSet();
				}
				catch(SQLException sE) {
					Terminal.say("The resultSet could not be retrieved. \n Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Build the GunsReplicas
				if(resSet != null) {
					result = BuildGunReplicas(resSet);
				}
				else {
					Terminal.say("No result was found");
				}
				
				//Close connection
				if(stmt != null) {
					try {
						stmt.close();
					}
					catch(SQLException sE) {
						Terminal.say("Stack trace: ");
						Terminal.say(sE.getStackTrace().toString());
					}
				}
				
				return result;
			}
			
			public ArrayList<GunReplicas> getAllGunsReplicas()
			{
				ArrayList<GunReplicas> allGunsReplicas = new ArrayList<GunReplicas>();
				
				//Create statement
				Statement stmt = null;
				try {
					stmt = conn.getDBcon().createStatement();
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Execute statement
				try {
					stmt.execute(selectAll);
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Retrieve results
				ResultSet resSet = null;
				try {
					resSet = stmt.getResultSet();
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Build an GunsReplicas object for each result
				try {
					do {
						allGunsReplicas.add(BuildGunReplicas(resSet));
					} while(resSet.next());
				}
				catch(SQLException sE) {
					Terminal.say("Stack Trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				//Close connection
				if(stmt != null) {
					try {
						stmt.close();
					}
					catch(SQLException sE) {
						Terminal.say("Stack Trace: ");
						Terminal.say(sE.getStackTrace().toString());
					}
				}
				
				return allGunsReplicas;
			}
			
			private String WhereAdder(String queryString, String condition)
			{
				return queryString + " WHERE " + condition;
			}
			
			private GunReplicas BuildGunReplicas(ResultSet resSet)
			{
				try {
					//ResultSet starts at -1, so a first next() call is needed to find the first item.
					if(resSet.next()) {
						Product p = dbP.getProduct(productID);
						
						String prodID = p.getProductID();
						String name = p.getName();
						double pPrice = p.getPurchasePrice();
						double sPrice = p.getSalesPrice();
						String cOO = p.getCountryOfOrigin();
						String prodType = p.getProductType();
						int stock = p.getStock();
						int minStock = p.getMinStock();
						
						String fabric = resSet.getString(this.fabric);
						String calibre = resSet.getString(this.calibre);
						
						return new GunReplicas(prodID, name, pPrice, sPrice, cOO, prodType, stock, minStock, fabric, calibre);
					}
					else {
						return null;
					}
				}
				catch(SQLException sE) {
					Terminal.say("Could not build GunReplicas object \n Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
					
					return null;
				}
			}

			public int DeleteGunReplicas(String id)
			{
				int rc = -1;

				Statement stmt = null;
				
				try {
					stmt = conn.getDBcon().createStatement();
					stmt.setQueryTimeout(5);
					rc = stmt.executeUpdate(WhereAdder(deleteGunReplicas, id));
					stmt.close();
				}
				catch(SQLException sE) {
					Terminal.say("Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				return(rc);
				
			}

			public int CreateGunReplicas(GunReplicas guns)
			{
				int rc = -1;
				String newID = String.format("SELECT MAX(CAST(value AS Int)) FROM %s WHERE VALUE NOT LIKE '%[a-z]%' AND ISNUMERIC(VALUE) = 1", "Product");
				newID += 10;
				/*String insertPQuery = createProduct + newID + ", " + guns.getName() + ", " + guns.getPurchasePrice() + ", " 
									+ guns.getSalesPrice() + ", " + guns.getCountryOfOrigin() + ", " + guns.getStock() + ", " + guns.getMinStock();*/
				String insertCQuery = createGunReplicas + newID + ", " + guns.getCalibre() + ", " + guns.getFabric();
				
				//Create statement
				Statement stmt = null;
				try {
					stmt = conn.getDBcon().createStatement();
				}
				catch(SQLException sE) {
					Terminal.say("An error occured during statement creation for insert. \n Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				
				//Execute statement
				try {
					if(dbP.InsertProduct(guns) < 0) {
						stmt.setQueryTimeout(5);
						rc = stmt.executeUpdate(insertCQuery);
					}
					else {
						Terminal.say("Error in insertProduct");
					}
				}
				catch(SQLException sE) {
					Terminal.say("An error occured executing the statement for insert.");
					Terminal.say(createGunReplicas );
				    Terminal.say("Stack trace: ");
					Terminal.say(sE.getStackTrace().toString());
				}
				
				
				//Close connection
				if(stmt != null) {
					try {
						stmt.close();
					}
					catch(SQLException sE) {
						Terminal.say("Stack trace: ");
						Terminal.say(sE.getStackTrace().toString());
					}
				}
				return (rc);
			}
			

			public void UpdateGunReplicas(String _prodID, String _fabric, String _calibre, String _name, double _pPrice, double _sPrice, String _country, String _prodType, int _stock, int _minStock)
			{
				DBProduct prod = new DBProduct();
				
				Statement stmt = null;
				try {
					stmt = conn.getDBcon().createStatement();
				}
				catch(SQLException sE) {
					Terminal.say("Could not create statement: \n" + sE.getStackTrace());
				}
				String updateQuery = String.format("UPdATE GunReplicas SET GProdID = '%s', Fabric = '%s', Calibre = '%s' FROM GunReplicas", 
						_prodID, _fabric, _calibre);
				
				try {
					stmt.executeQuery(WhereAdder(updateQuery, "GProdID = " + _prodID));
					stmt.setQueryTimeout(5);
					prod.updateProduct(_prodID, _name, _pPrice, _sPrice, _country, _prodType, _stock, _minStock);
				}
				catch(SQLException sE) {
					Terminal.say("Error in executing: \n" + updateQuery);
				}

				try {
					stmt.close();
				} catch (SQLException e) {
					Terminal.say("Close Statement error", e);
				}
			}
}
